﻿using System;
using System.Collections.Generic;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;

namespace SWSP.Management.ServerManager
{
    /// <summary>
    /// This class is a wrapper of SqlServer.Management for easy server managment.
    /// </summary>
    public class SqlServerManager
    {

        #region Private Properties

        private Server SqlServer { get; set; }
        
        private ServerConnection SqlServerConnetion {get; set;}
        
        private SqlConnection SqlDbConnection {get; set;}

        #endregion

        #region Public Properties

        /// <summary>
        /// Sql server type
        /// </summary>
        public enum ServerType { SqlServer, OracleServer, MySqlServer };

        /// <summary>
        /// Return banner information (Server name, Server version, Server login).
        /// </summary>
        public string Banner
        {
            get
            {
                return String.Format("SQL Server {0} - {1} - {2}", SqlServer.Information.VersionString, SqlServer.InstanceName, SqlServer.ConnectionContext.TrueLogin);
            }
        }

        /// <summary>
        /// Return a list of DataBase.
        /// </summary>
        public DatabaseCollection Databases
        {
            get
            {
                return SqlServer.Databases;
            }
        }

        /// <summary>
        /// Get/Set the current connection string information.
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// Type of server
        /// </summary>
        public ServerType Type { get; set; }

        #endregion

        #region Ctor

        /// <summary>
        /// SqlServerManager costructor.
        /// </summary>
        public SqlServerManager(ServerType type)
        {
            this.Type = type;
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// Open the connection. Connection string property must be setted before open connection.
        /// </summary>
        public void OpenConnection()
        {
            SqlDbConnection = new SqlConnection(ConnectionString);
            SqlServerConnetion = new ServerConnection(SqlDbConnection);
            SqlServer = new Server(SqlServerConnetion);
        }

        /// <summary>
        /// Open the connection with a specified connection string.
        /// </summary>
        /// <param name="connectionString"></param>
        public void OpenConnection(string connectionString)
        {
            ConnectionString = connectionString;
            SqlDbConnection = new SqlConnection(ConnectionString);
            SqlServerConnetion = new ServerConnection(SqlDbConnection);
            SqlServer = new Server(SqlServerConnetion);
        }

        /// <summary>
        /// Return a specified database.
        /// </summary>
        /// <param name="databaseName">data base name</param>
        /// <returns>MicrosoftSQL database</returns>
        public Database GetDatabase(string databaseName)
        {
            return SqlServer.Databases[databaseName]; 
        }

        /// <summary>
        /// Return all Table for a specified database
        /// </summary>
        /// <param name="databaseName"></param>
        /// <returns>MicrosoftSQL table collection</returns>
        public TableCollection GetDatabaseTables(string databaseName)
        {
            return SqlServer.Databases[databaseName].Tables;
        }

        public StoredProcedureCollection GetDatabaseProcedures(string databaseName)
        {
            return SqlServer.Databases[databaseName].StoredProcedures;
        }

        public UserDefinedFunctionCollection GetDatabaseFunctions(string databaseName)
        {
            return SqlServer.Databases[databaseName].UserDefinedFunctions;
        }


        public ViewCollection GetDataBaseViews(string databaseName)
        {
            return SqlServer.Databases[databaseName].Views;
        }

        public ColumnCollection GetTableColumns(string databaseName, string tableName)
        {
            return SqlServer.Databases[databaseName].Tables[tableName].Columns;
        }

        #endregion

        public void Close()
        {
            SqlDbConnection.Close();
            
        }
    }
}
